import os
from flask import Blueprint, render_template, request, redirect, url_for, flash
from database import get_db

# 创建模型库管理Blueprint
model_bp = Blueprint('model', __name__, url_prefix='/models')

@model_bp.route('/')
def model_list():
    search_query = request.args.get('search', '').strip()
    category_id = request.args.get('category_id', '').strip()  # 获取分类筛选参数
    db = get_db()
    
    try:
        # 获取模型分类列表（用于筛选下拉框）
        categories = db.execute(
            '''WITH RECURSIVE category_hierarchy AS (
                   SELECT id, name, parent_id, 
                          CAST(printf('%03d', id) AS TEXT) AS hierarchy_path, 
                          1 AS level
                   FROM model_categories
                   WHERE parent_id = 0 OR parent_id IS NULL
                   UNION ALL
                   SELECT cc.id, cc.name, cc.parent_id, 
                          ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                          ch.level + 1 AS level
                   FROM model_categories cc
                   JOIN category_hierarchy ch ON cc.parent_id = ch.id
               )
               SELECT id, name, level, hierarchy_path
               FROM category_hierarchy
               ORDER BY hierarchy_path'''
        ).fetchall()
        
        # 构建查询条件
        query_params = []
        where_clauses = []
        
        if search_query:
            where_clauses.append('(m.emission_source LIKE ? OR f.name LIKE ?)')
            query_params.extend(['%' + search_query + '%', '%' + search_query + '%'])
        
        if category_id and category_id.isdigit():
            where_clauses.append('m.category_id = ?')
            query_params.append(category_id)
        
        # 基础查询SQL
        base_sql = '''
            SELECT m.*, f.name as factor_name, 
                   CONCAT_WS('->', 
                     (SELECT name FROM factor_categories WHERE id = fc.parent_id), 
                     fc.name) as factor_category_path, 
                   mc.name as model_category_name,  -- 模型分类名称
                   CONCAT_WS('->', 
                     (SELECT name FROM model_categories WHERE id = mcc.parent_id), 
                     mcc.name) as model_category_path  -- 模型分类路径
            FROM models m 
            JOIN carbon_factors f ON m.factor_id = f.id 
            LEFT JOIN factor_categories fc ON f.category_id = fc.id 
            LEFT JOIN model_categories mc ON m.category_id = mc.id 
            LEFT JOIN model_categories mcc ON m.category_id = mcc.id 
        '''
        
        # 添加WHERE子句
        if where_clauses:
            base_sql += ' WHERE ' + ' AND '.join(where_clauses)
        
        # 添加排序
        base_sql += ' ORDER BY m.created_at DESC'
        
        # 执行查询
        models = db.execute(base_sql, query_params).fetchall()
    except Exception as e:  # 修改为通用异常捕获，与factors.py保持一致
        flash(f'数据库错误: {str(e)}', 'danger')
        models = []
        categories = []
    
    return render_template('models.html', models=models, search_query=search_query, 
                           categories=categories, selected_category=category_id)  # 传递分类数据和选中状态

@model_bp.route('/add', methods=['GET', 'POST'])
def add_model():
    db = get_db()
    
    if request.method == 'POST':
        emission_source = request.form['emission_source']
        description = request.form['description']
        data_source = request.form['data_source']
        emission_unit = request.form['emission_unit']
        factor_id = request.form['factor_id']
        category_id = request.form['category_id']  # 获取模型分类ID
        
        # 验证必填字段
        if not emission_source or not emission_unit or not factor_id or not category_id:  # 添加分类验证
            flash('请填写所有必填字段', 'danger')
            return redirect(url_for('model.add_model'))
        
        # 插入模型记录（新增category_id字段）
        db.execute(
            'INSERT INTO models (emission_source, description, data_source, emission_unit, factor_id, category_id) '
            'VALUES (?, ?, ?, ?, ?, ?)',
            (emission_source, description, data_source, emission_unit, factor_id, category_id)
        )
        db.commit()
        flash('模型添加成功', 'success')
        return redirect(url_for('model.model_list'))
    
    # 获取所有碳排放因子
    factors = db.execute(
        'SELECT f.id, f.name, '
        'CONCAT_WS(\'->\', '
        '  (SELECT name FROM factor_categories WHERE id = fc.parent_id), '
        '  fc.name) as category_path '
        'FROM carbon_factors f '
        'LEFT JOIN factor_categories fc ON f.category_id = fc.id '
        'ORDER BY category_path'
    ).fetchall()
    
    # 获取模型分类列表（新增）
    categories = db.execute(
        '''WITH RECURSIVE category_hierarchy AS (
               SELECT id, name, parent_id, 
                      CAST(printf('%03d', id) AS TEXT) AS hierarchy_path, 
                      1 AS level
               FROM model_categories
               WHERE parent_id = 0 OR parent_id IS NULL
               UNION ALL
               SELECT cc.id, cc.name, cc.parent_id, 
                      ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                      ch.level + 1 AS level
               FROM model_categories cc
               JOIN category_hierarchy ch ON cc.parent_id = ch.id
           )
           SELECT id, name, level, hierarchy_path
           FROM category_hierarchy
           ORDER BY hierarchy_path'''
    ).fetchall()
    
    return render_template('add_model.html', factors=factors, categories=categories)  # 传递分类数据

@model_bp.route('/edit/<int:id>', methods=['GET', 'POST'])
def edit_model(id):
    db = get_db()
    model = db.execute('SELECT * FROM models WHERE id = ?', (id,)).fetchone()
    
    if model is None:
        flash('模型不存在', 'danger')
        return redirect(url_for('model.model_list'))
    
    if request.method == 'POST':
        emission_source = request.form['emission_source']
        description = request.form['description']
        data_source = request.form['data_source']
        emission_unit = request.form['emission_unit']
        factor_id = request.form['factor_id']
        category_id = request.form['category_id']  # 获取模型分类ID
        
        # 验证必填字段
        if not emission_source or not emission_unit or not factor_id or not category_id:  # 添加分类验证
            flash('请填写所有必填字段', 'danger')
            return redirect(url_for('model.edit_model', id=id))
        
        # 更新模型记录（新增category_id字段）
        db.execute(
            'UPDATE models SET emission_source = ?, description = ?, data_source = ?, emission_unit = ?, factor_id = ?, category_id = ? WHERE id = ?',
            (emission_source, description, data_source, emission_unit, factor_id, category_id, id)
        )
        db.commit()
        flash('模型更新成功', 'success')
        return redirect(url_for('model.model_list'))
    
    # 获取所有碳排放因子
    factors = db.execute(
        'SELECT f.id, f.name, '
        'CONCAT_WS(\'->\', '
        '  (SELECT name FROM factor_categories WHERE id = fc.parent_id), '
        '  fc.name) as category_path '
        'FROM carbon_factors f '
        'LEFT JOIN factor_categories fc ON f.category_id = fc.id '
        'ORDER BY category_path'
    ).fetchall()
    
    # 获取模型分类列表（新增）
    categories = db.execute(
        '''WITH RECURSIVE category_hierarchy AS (
               SELECT id, name, parent_id, 
                      CAST(printf('%03d', id) AS TEXT) AS hierarchy_path, 
                      1 AS level
               FROM model_categories
               WHERE parent_id = 0 OR parent_id IS NULL
               UNION ALL
               SELECT cc.id, cc.name, cc.parent_id, 
                      ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                      ch.level + 1 AS level
               FROM model_categories cc
               JOIN category_hierarchy ch ON cc.parent_id = ch.id
           )
           SELECT id, name, level, hierarchy_path
           FROM category_hierarchy
           ORDER BY hierarchy_path'''
    ).fetchall()
    
    return render_template('edit_model.html', model=model, factors=factors, categories=categories)  # 传递分类数据

@model_bp.route('/delete/<int:id>', methods=['POST'])
def delete_model(id):
    db = get_db()
    
    # 检查模型是否存在
    model = db.execute('SELECT id FROM models WHERE id = ?', (id,)).fetchone()
    if model is None:
        flash('模型不存在', 'danger')
        return redirect(url_for('model.model_list'))
    
    # 删除模型
    db.execute('DELETE FROM models WHERE id = ?', (id,))
    db.commit()
    flash('模型删除成功', 'success')
    return redirect(url_for('model.model_list'))